using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SQLite;
using NewsSpider.Model;
using NewsSpider.DBSqlHelp;

namespace NewsSpider.DAL
{

    /// <summary>
    /// 版权所有: 版权所有(C) 2012，上海云里网络科技有限公司
    /// 内容摘要: CatchWebsitesSQLiteDAL
    /// 完成日期：2012年1月31日
    /// 版    本：V1.0 
    /// 作    者：Adin
    /// </summary>
    public class CatchWebsitesSQLiteDAL
    {

        /// <summary>
        /// 获取CatchWebsites表中数据
        /// <returns>返回List</returns>
        /// </summary>
        public List<CatchWebsites> GetAllCatchWebsitesDAL()
        {
            string strSql = "select * from CatchWebsites";
            List<CatchWebsites> list = new List<CatchWebsites>();
            try
            {
                SQLiteConnection cn = new SQLiteConnection(NewsSpider.DBSqlHelp.DBSQLiteHelper.ConnectionSQLite());
                cn.SetPassword(NewsSpider.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                SQLiteDataReader dr = cm.ExecuteReader();
                while (dr.Read())
                {
                    CatchWebsites cs = new CatchWebsites();
                    cs.SiteId = int.Parse(dr["site_id"].ToString());
                    cs.SiteName = dr["site_name"].ToString();
                    cs.SiteUrl = dr["site_url"].ToString();
                    cs.TagStartList = dr["tag_start_list"].ToString();
                    cs.TagEndList = dr["tag_end_list"].ToString();
                    cs.TagLinkStart = dr["tag_link_start"].ToString();
                    cs.TagLinkEnd = dr["tag_link_end"].ToString();
                    cs.TagStartTitle = dr["tag_start_title"].ToString();
                    cs.TagEndTitle = dr["tag_end_title"].ToString();
                    cs.TagStartContent = dr["tag_start_content"].ToString();
                    cs.TagEndContent = dr["tag_end_content"].ToString();
                    cs.TagStartAuthor = dr["tag_start_author"].ToString();
                    cs.TagEndAuthor = dr["tag_end_author"].ToString();
                    cs.TagStartSource = dr["tag_start_source"].ToString();
                    cs.TagEndSource = dr["tag_end_source"].ToString();
                    cs.PageCharset = dr["page_charset"].ToString();

                    list.Add(cs);
                }
                dr.Dispose();
                cn.Close();
                return list;
            }
            catch (Exception ex)
            {
                NewsSpider.Utility.Log.SaveErrorLog("绑定CatchWebsites -> " + ex.Message);
            }

            return list;
        }

        /// <summary>
        /// 根据指定SQL语句获取CatchWebsites表中数据
        /// <param name="strSql">SQL语句</param>
        /// </summary>
        /// <returns>返回List</returns>
        public List<CatchWebsites> GetCatchWebsitesByWhereDAL(string strSql)
        {
            List<CatchWebsites> list = new List<CatchWebsites>();
            try
            {
                SQLiteConnection cn = new SQLiteConnection(NewsSpider.DBSqlHelp.DBSQLiteHelper.ConnectionSQLite());
                cn.SetPassword(NewsSpider.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                SQLiteDataReader dr = cm.ExecuteReader();
                while (dr.Read())
                {
                    CatchWebsites cs = new CatchWebsites();
                    cs.SiteId = int.Parse(dr["site_id"].ToString());
                    cs.SiteName = dr["site_name"].ToString();
                    cs.SiteUrl = dr["site_url"].ToString();
                    cs.TagStartList = dr["tag_start_list"].ToString();
                    cs.TagEndList = dr["tag_end_list"].ToString();
                    cs.TagLinkStart = dr["tag_link_start"].ToString();
                    cs.TagLinkEnd = dr["tag_link_end"].ToString();
                    cs.TagStartTitle = dr["tag_start_title"].ToString();
                    cs.TagEndTitle = dr["tag_end_title"].ToString();
                    cs.TagStartContent = dr["tag_start_content"].ToString();
                    cs.TagEndContent = dr["tag_end_content"].ToString();
                    cs.TagStartAuthor = dr["tag_start_author"].ToString();
                    cs.TagEndAuthor = dr["tag_end_author"].ToString();
                    cs.TagStartSource = dr["tag_start_source"].ToString();
                    cs.TagEndSource = dr["tag_end_source"].ToString();
                    cs.PageCharset = dr["page_charset"].ToString();

                    list.Add(cs);
                }
                dr.Dispose();
                cn.Close();
                return list;
            }
            catch (Exception ex)
            {
                NewsSpider.Utility.Log.SaveErrorLog("绑定CatchWebsites -> " + ex.Message);
            }

            return list;
        }

        /// <summary>
        /// 往CatchWebsites表中插入数据
        /// <param name="asnew">实例化CatchWebsites类</param>
        /// </summary>
        /// <returns>返回影响行数</returns>
        public int InsertCatchWebsitesDAL(NewsSpider.Model.CatchWebsites cs)
        {
            int iNum = 0;
            string strSql = "insert into CatchWebsites(site_id,site_name,site_url,tag_start_list,tag_end_list,tag_link_start,tag_link_end,tag_start_title,tag_end_title,tag_start_content,tag_end_content,tag_start_author,tag_end_author,tag_start_source,tag_end_source,page_charset) " +
             " values(" + GetAutoAddNum() + ",'" + cs.SiteName + "','" + cs.SiteUrl + "','" + cs.TagStartList + "','" + cs.TagEndList + "','" + cs.TagLinkStart + "','" + cs.TagLinkEnd + "','" + cs.TagStartTitle + "','" + cs.TagEndTitle + "','" + cs.TagStartContent + "','" + cs.TagEndContent + "','" + cs.TagStartAuthor + "','" + cs.TagEndAuthor + "','" + cs.TagStartSource + "','" + cs.TagEndSource + "','" + cs.PageCharset + "')";
            try
            {
                return NewsSpider.DBSqlHelp.DBSQLiteHelper.ExecuteSql(strSql);
            }
            catch (Exception ex)
            {
                NewsSpider.Utility.Log.SaveErrorLog("绑定CatchWebsites -> " + ex.Message);
            }
            return iNum;
        }

        /// <summary>
        /// 根据指定条件删除CatchWebsites表中数据
        /// <param name="strSql">SQL语句</param>
        /// </summary>
        /// <returns>返回影响行数</returns>
        public int DeleteCatchWebsitesDAL(string strSql)
        {
            int iNum = 0;
            try
            {
                iNum = NewsSpider.DBSqlHelp.DBSQLiteHelper.ExecuteSql(strSql);
            }
            catch (Exception ex)
            {
                NewsSpider.Utility.Log.SaveErrorLog("绑定CatchWebsites -> " + ex.Message);
            }

            return iNum;
        }

        /// <summary>
        /// 更新CatchWebsites表中数据
        /// </summary>
        /// <param name="asnew">实例化CatchWebsites类</param>
        /// <returns>返回影响行数</returns>
        public int UpdateCatchWebsitesDAL(NewsSpider.Model.CatchWebsites cs)
        {
            int iNum = 0;
            string strSql = "update CatchWebsites set site_name='" + cs.SiteName + "',site_url='" + cs.SiteUrl + "',tag_start_list='" + cs.TagStartList + "',tag_end_list='" + cs.TagEndList + "',tag_link_start='" + cs.TagLinkStart + "',tag_link_end='" + cs.TagLinkEnd + "',tag_start_title='" + cs.TagStartTitle + "',tag_end_title='" + cs.TagEndTitle + "',tag_start_content='" + cs.TagStartContent + "',tag_end_content='" + cs.TagEndContent + "',tag_start_author='" + cs.TagStartAuthor + "',tag_end_author='" + cs.TagEndAuthor + "',tag_start_source='" + cs.TagStartSource + "',tag_end_source='" + cs.TagEndSource + "' where site_id=" + cs.SiteId;
            try
            {
                iNum = NewsSpider.DBSqlHelp.DBSQLiteHelper.ExecuteSql(strSql);
            }
            catch (Exception ex)
            {
                NewsSpider.Utility.Log.SaveErrorLog("绑定CatchWebsites -> " + ex.Message);
            }
            return iNum;
        }

        /// <summary>
        /// 绑定表CatchWebsites到DataGridView
        /// <param name="dgv">DataGridView控件名称</param>
        /// <param name="strSql">SQL语句</param>
        /// </summary>
        public void BindCatchWebsitesDAL(System.Windows.Forms.DataGridView dgv, string strSql)
        {
            try
            {
                //清除最后自动生成的行
                dgv.AutoGenerateColumns = false;
                SQLiteConnection cn = new SQLiteConnection(NewsSpider.DBSqlHelp.DBSQLiteHelper.ConnectionSQLite());
                cn.SetPassword(NewsSpider.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                SQLiteDataAdapter da = new SQLiteDataAdapter(cm);
                DataSet ds = new DataSet();
                da.Fill(ds, "CatchWebsites");
                ds.Tables["CatchWebsites"].Columns.Add("HasCatch", typeof(string));
                for (int i = 0; i < ds.Tables["CatchWebsites"].Rows.Count; i++)
                {
                    DataRow dr = ds.Tables["CatchWebsites"].Rows[i];
                    string strCatch = dr["is_catch"].ToString();
                    if (strCatch == "1")
                    {
                        dr["HasCatch"] = "是";
                    }
                    else
                    {
                        dr["HasCatch"] = "否";
                    }
                }
                dgv.DataSource = ds.Tables["CatchWebsites"].DefaultView;
                da.Dispose();
                cn.Close();
                //清除当前选择行行为
                dgv.CurrentCell = null;

            }
            catch (Exception ex)
            {
                NewsSpider.Utility.Log.SaveErrorLog("绑定CatchWebsites -> " + ex.Message);
            }
        }

        /// <summary>
        /// 获取表中最大ID，并自增1
        /// </summary>
        /// <returns>返回表中最大ID，并自增1</returns>
        public int GetAutoAddNum()
        {
            int iNum = 0;
            try
            {
                SQLiteConnection cn = new SQLiteConnection(NewsSpider.DBSqlHelp.DBSQLiteHelper.ConnectionSQLite());
                cn.SetPassword(NewsSpider.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                string strSql = "select site_id from CatchWebsites order by site_id desc limit 1";
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                SQLiteDataReader dr = cm.ExecuteReader();
                if (dr.Read())
                {
                    iNum = int.Parse(dr["site_id"].ToString()) + 1;
                }
                else
                {
                    iNum = 1;
                }
                dr.Dispose();
                cn.Close();
            }
            catch (Exception ex)
            {
                NewsSpider.Utility.Log.SaveErrorLog("绑定CatchWebsites -> " + ex.Message);
            }

            return iNum;
        }

        /// <summary>
        /// 检测是否已存在数据库中，屏蔽已抓取的数据
        /// </summary>
        /// <param name="strCatchURL">原文链接</param>
        /// <returns>返回 True or False</returns>
        public bool CheckArticleIsExist(string strCatchURL)
        {
            bool bValue = false;
            try
            {
                SQLiteConnection cn = new SQLiteConnection(NewsSpider.DBSqlHelp.DBSQLiteHelper.ConnectionSQLite());
                cn.SetPassword(NewsSpider.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                string strSql = "select site_id from CatchWebsites where site_url='" + strCatchURL + "'";
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                SQLiteDataReader dr = cm.ExecuteReader();
                if (dr.Read())
                {
                    bValue = true;
                }
                else
                {
                    bValue = false;
                }
                dr.Dispose();
                cn.Close();
            }
            catch (Exception ex)
            {
                NewsSpider.Utility.Log.SaveErrorLog("检测是否已存在数据库中 -> " + ex.Message);
            }

            return bValue;
        }

        /// <summary>
        /// 更新应用安装状态
        /// </summary>
        /// <param name="strSiteType">站点类型</param>
        /// <returns></returns>
        public bool UpdateCatchWebsitesSetupStatus(string strSiteType)
        {
            bool bValue = false;
            try
            {
                SQLiteConnection cn = new SQLiteConnection(NewsSpider.DBSqlHelp.DBSQLiteHelper.ConnectionSQLite());
                cn.SetPassword(NewsSpider.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                string strSql = "update CatchWebsites set setup_status=2 where site_type=" + strSiteType + "";
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                cm.ExecuteNonQuery();
                cn.Close();
            }
            catch (Exception ex)
            {
                NewsSpider.Utility.Log.SaveErrorLog("更新应用安装状态 -> " + ex.Message);
            }

            return bValue;
        }

        public bool CheckSiteTypeIsExist(string strSiteType)
        {
            bool bValue = false;
            try
            {
                SQLiteConnection cn = new SQLiteConnection(NewsSpider.DBSqlHelp.DBSQLiteHelper.ConnectionSQLite());
                cn.SetPassword(NewsSpider.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                string strSql = "select setup_status from CatchWebsites where site_type=" + strSiteType + " order by site_id";
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                SQLiteDataReader dr = cm.ExecuteReader();
                if (dr.Read())
                {
                    if (dr["setup_status"].ToString() == "2")
                    {
                        bValue = true;
                    }
                    else
                    {
                        bValue = false;
                    }
                }
                else
                {
                    bValue = false;
                }
                dr.Dispose();
                cn.Close();
            }
            catch (Exception ex)
            {
                NewsSpider.Utility.Log.SaveErrorLog("检测是否已存在数据库中 -> " + ex.Message);
            }

            return bValue;
        }
    }
}
